package classes;

import util.DBUtil;
import util.Debug;
import java.sql.*;
import java.util.*;
import java.sql.Date;

public class Student {
	
  private int sid;
  public  String fname;
  public  String lname;
  private String email;
  public  String parent_email;
  public  String school_name;
  public  String location;
  public  String picture_location;
  public  String bio;
  public  int grade;
  public  Date dob; 
  public  boolean above_13;
  public  boolean bio_visable;
  public  boolean dob_visable;
  public  boolean schooledinid_visable;
  public  String schooledinid;
  public  ArrayList<Classmate> classmates;
  public  ArrayList<Teacher> teachers;
  public  ArrayList<MyDreamList> dreamlist;
  public  ArrayList<AcademicList> academiclist;
  public  DBUtil db;
   
  public Student(int sid, String fname, String lname) {
    this.sid = sid;
    this.fname = fname;
    this.lname = lname;
  }
  
  public Student(int sid) {
	fname = "";
	lname = "";
	email = "";
	parent_email = "";
	school_name = "";
	location = "";
	bio = "";
	schooledinid = "";
	grade = 0;
	dob = null;
	above_13 = false;
	bio_visable = false;
	dob_visable = false;
	schooledinid_visable = false;
	db = new DBUtil();
	this.sid = sid;
	classmates = new ArrayList<Classmate>();
	teachers   = new ArrayList<Teacher>();
	dreamlist  = new ArrayList<MyDreamList>(); 
	academiclist  = new ArrayList<AcademicList>();
  }
  
  public int getSID() {
    return sid;
  }
  
  public void loadData() {
	db.connect();
	String sql = null;
	sql = "SELECT FNAME,LNAME,SCHOOL_NAME,LOCATION,GRADE,BIO," + 
	      "PICTURE_LOCATION,SCHOOLEDINID,BIO_VISABLE,DOB_VISABLE,SCHOOLEDINID_VISABLE " +
	      "FROM SCHOOLEDIN.STUDENTS WHERE SID = ?";
	PreparedStatement pstmt = null, pstmt2 = null, pstmt3 = null, pstmt4 = null, pstmt5 = null;
	ResultSet rs = null, rs2 = null, rs3 = null, rs4 = null, rs5 = null;
	Debug.log("Exec: " + sql);
	
	try {  
	  pstmt = db.conn.prepareStatement(sql);
	  pstmt.setInt(1,sid); 
	  rs = pstmt.executeQuery();
	  while(rs.next()) {
	    fname = rs.getString(1);
	    lname = rs.getString(2);
	    school_name = rs.getString(3);
	    location = rs.getString(4);
	    grade = rs.getInt(5);
	    bio = rs.getString(6) == null ? "" : rs.getString(6);
	    picture_location = rs.getString(7);
	    schooledinid = rs.getString(8);
	    bio_visable = rs.getBoolean(9);
	    dob_visable = rs.getBoolean(10);
	    schooledinid_visable = rs.getBoolean(11);
	  }
	   
	  /* Select classmate list for this student */
	  sql = null;
	  sql ="SELECT S2.SID, S2.FNAME, S2.LNAME, S2.GRADE, S2.LOCATION " +
	       "FROM SCHOOLEDIN.STUDENTS S1, SCHOOLEDIN.STUDENTS S2, SCHOOLEDIN.CLASSMATES C " +
		   "WHERE S1.SID = ? " +
           "AND S1.SID = C.SID " +
           "AND C.CMID = S2.SID";
	  
	  Debug.log("Retreive classmate list: " + sql);
	  pstmt2 = db.conn.prepareStatement(sql);
	  pstmt2.setInt(1,sid);
	  Debug.log("executeQuery()");
	  rs2 = pstmt2.executeQuery();
	  while(rs2.next()) {
		classmates.add(new Classmate(rs2.getInt(1),rs2.getString(2),rs2.getString(3),rs2.getInt(4),rs2.getString(5)));
		Debug.log("Added new classmate");
	  }
	  Debug.log("Classmate size =" + classmates.size());
	  
	  /* Select teacher list for this student */
	  sql = null;
	  sql ="SELECT T.TID, T.FNAME, T.LNAME, T.SCHOOL_NAME, T.LOCATION " +
           "FROM SCHOOLEDIN.STUDENTS S1, SCHOOLEDIN.TEACHERS T, " + 
		   "SCHOOLEDIN.STUDENT_TEACHER_RELATIONSHIP STR " +
	       "WHERE S1.SID = ? " +
           "AND S1.SID = STR.SID " +
           "AND STR.TID = T.TID";
	  Debug.log("Retreive teacher list: " + sql);
	  Debug.log("SID = ?" + sid);
	  pstmt3 = db.conn.prepareStatement(sql);
	  pstmt3.setInt(1,sid);
	  Debug.log("executeQuery()");
	  rs3 = pstmt3.executeQuery();
	  while(rs3.next()) {
		teachers.add(new Teacher(rs3.getInt(1),rs3.getString(2),rs3.getString(3),rs3.getString(4),rs3.getString(5)));
		Debug.log("Added new Teacher");
	  }
	  Debug.log("Teacher size =" + teachers.size()); 
	  
	  
	  /*select dreamlist for this student */
	  sql = null;
	  sql = " SELECT SC.SCHOOL_NAME,SC.LOCATION " + 
	        "FROM SCHOOLEDIN.STUDENTS S, SCHOOLEDIN.DREAMLIST D, SCHOOLEDIN.SCHOOLS SC " +
			"WHERE S.SID = ? " +
	        "AND S.SID = D.SID " +
			"AND D.SCID = SC.SCID";
	  pstmt4 = db.conn.prepareStatement(sql);
	  pstmt4.setInt(1,sid);
	  rs4 = pstmt4.executeQuery();
	  while(rs4.next()) {
	    dreamlist.add(new MyDreamList(rs4.getString(1),rs4.getString(2)));
	  }
	  
	  sql = null;
	  sql = "SELECT AP.YEAR, AP.GRADE,AP.SCHOOL_NAME,AP.SUMMARY " +
	         "FROM SCHOOLEDIN.STUDENTS S, SCHOOLEDIN.ACADEMIC_PROFILE AP " +
	         "WHERE S.SID = ? " +
	         "AND S.SID = AP.SID";
	  
	  /*select academic list for this student */
      pstmt5 = db.conn.prepareStatement(sql);
      pstmt5.setInt(1,sid);
      rs5 = pstmt5.executeQuery();
      while(rs5.next()) {
        academiclist.add(new AcademicList(rs5.getInt(1),rs5.getInt(2),rs5.getString(3),rs5.getString(4)));
      }
	} 
	catch(SQLException e) {
	   Debug.log("Catch:....");
	   Debug.loge(e);
	}
	finally {
	  try {
	    rs.close();
	    pstmt.close();
	    rs2.close();
	    pstmt2.close();
	    rs3.close();
	    pstmt3.close();
	    rs4.close();
	    pstmt4.close();
	    rs5.close();
	    pstmt5.close();
	    db.connect();
	    db.close();
	  }
	  catch(SQLException e) {
	    Debug.log("Students.loadData():");
	    Debug.loge(e);
	  }
	}
  }
  public String getUserName() {
	return email;  
  }
  
  public void updateBIO(String bio) {
	  
	Debug.log("update BIO");
    db.connect();
    String sql = "UPDATE SCHOOLEDIN.STUDENTS " +
    		     "SET BIO = ? " +
    		     "WHERE SID = ?";
    PreparedStatement pstmt = null;
    Debug.log("Exec: " + sql);
    Debug.log("bio=" + bio);
    Debug.log("sid=" + sid);
    
    try {
      pstmt = db.conn.prepareStatement(sql);
      pstmt.setString(1,bio);
      pstmt.setInt(2,sid);
      pstmt.executeUpdate();  
    }
    catch(SQLException e) {
      Debug.loge(e);
    }
    finally {
      try {
        Debug.log("call commit");
    	db.conn.commit();
        pstmt.close();
        db.conn.close();
      }
      catch(SQLException e2) {
        Debug.log("Students.updateBIO():");
        Debug.loge(e2);
      }
    }
    this.bio = bio;
  }
   
  public void updatePublicNobs() {
	Debug.log("update visible nobs.");
	db.connect();
	String sql = "UPDATE SCHOOLEDIN.STUDENTS " +
	    		     "SET BIO_VISABLE = ?, DOB_VISABLE = ?, SCHOOLEDINID_VISABLE = ? " +
	    		     "WHERE SID = ?";
	PreparedStatement pstmt = null;
	Debug.log("Exec: " + sql);
	     
	try {
	  pstmt = db.conn.prepareStatement(sql);
	  pstmt.setBoolean(1,bio_visable);
	  pstmt.setBoolean(2,dob_visable);
	  pstmt.setBoolean(3,schooledinid_visable);
	  pstmt.setInt(4,sid);
	  pstmt.executeUpdate();  
	}
	catch(SQLException e) {
	  Debug.loge(e);
	}
	finally {
	  try {
	    pstmt.close();
	    db.conn.commit();
	    db.conn.close();
	  }
	  catch(SQLException e2) {
	    Debug.log("Students.updatePicture():");
	    Debug.loge(e2);
	  }
	}  
  }
  
  public void updatePicture(String fileName) {
	Debug.log("update picture");
    db.connect();
    String sql = "UPDATE SCHOOLEDIN.STUDENTS " +
    		     "SET PICTURE_LOCATION = ? " +
    		     "WHERE SID = ?";
    PreparedStatement pstmt = null;
    Debug.log("Exec: " + sql);
    
    try {
      pstmt = db.conn.prepareStatement(sql);
      pstmt.setString(1,fileName);
      pstmt.setInt(2,sid);
      pstmt.executeUpdate();  
    }
    catch(SQLException e) {
      Debug.loge(e);
    }
    finally {
      try {
        pstmt.close();
        db.conn.commit();
        db.conn.close();
      }
      catch(SQLException e2) {
        Debug.log("Students.updatePicture():");
        Debug.loge(e2);
      }
    }
  }
}